package com.caracol.db;

import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

/**
 * Clase que realiza el acceso a la base de datos en el gestor Postgresql esta
 * clase esta basada en la clase de Beatriz A. Olivares Zepahua.
 * Driver, sUrl, sUsr, sPwd están fijos, pero deben leerse de un archivo
 * encriptado y que sea externo por pruebas se quedaran en hardcode por el 
 * momento
 * 
 * @author Roque Manuel Rueda Anastacio
 * @version 2.0
 */
public final class AccesoDatos {

    private static String sUrl = null;  // sUrl de conexión
    private static String sUsr = null; // Usuario
    private static String sPwd = null;  // Contraseña
    private java.sql.Connection oConexion;  // La conexión

    public AccesoDatos() throws Exception {
        // TODO: this should be read from a xml file!!
        sUrl = "jdbc:postgresql://localhost/caracol";
        sUsr = "usrcaracol";
        sPwd = "caracol";
    }

    /**
     * Realiza la conexión a la base de datos.
     */
    public void conectar() throws Exception {

        try {
            Class.forName("org.postgresql.Driver").newInstance();
            oConexion = DriverManager.getConnection(sUrl, sUsr, sPwd);
        } catch (SQLException e) {
            throw e;
        }
    }

    /**
     * Realiza la desconexión a la base de datos.
     */
    public void desconectar() throws Exception {
        oConexion.close();
    }

    /**
     * Código que se ejecuta cuando este objeto es colectado.
     */
    @Override
    public void finalize() throws Exception {
        oConexion.close();
        oConexion = null;
    }

    /**
     * Realiza una consulta a la base de datos y retorna un vector de resultados.
     */
    public synchronized Vector ejecutarConsulta(String psQuery) throws Exception {

        Statement stmt = null;
        ResultSet rset = null;
        Vector vrset = null;
        ResultSetMetaData rsmd = null;
        int nNumCols = 0;
        try {
            stmt = oConexion.createStatement();
            rset = stmt.executeQuery(psQuery);
            rsmd = rset.getMetaData();
            nNumCols = rsmd.getColumnCount();
            vrset = convierteAVector(rset, rsmd, nNumCols);
        } finally {
            if (rset != null) {
                rset.close();
                stmt.close();
            }
            rset = null;
            stmt = null;
        }
        return vrset;
    }

    /**
     * Ejecuta un consulta a la base de datos regresando una lista de objetos 
     * con los valores obtenidos y cerrando la conexion
     * 
     * Executes a query in the database and returns a List of Object with the
     * returned values from the Database and closing the connection
     * 
     * @param colums Array of strings that contains all the colums if null is
     * passed this select all the colums in the table
     * @param tableName Name of the table to select
     * @param condition Map<String,String> this is the selection to be aplied
     * in the database and should be in the forma key as column name and value
     * as 'X'value that should contain the database if null no selection is aply
     * @param groupBy this is the group by expression if null no group by is aply
     * @param having having setence to be aply
     * @param orderBy order by setence to be aply
     * @param limit limit if records setence to be aply
     * @return List of Object with the result
     * @exception SQLException if some database exception occurs
     * @exception Exception if something other than database exception occurs
     */
    public synchronized java.util.List<Object> query(String[] colums, String tableName,
            Map<String, String> conditions /* selection should be column as key and 'X'value as value of map */,
            String groupBy, String having, String orderBy, String limit) throws SQLException, Exception {
        
        return rawQuery(createQueryString(colums, tableName, conditions, groupBy, having, orderBy, limit));
    }
    
    /**
     * Ejecuta un consulta a la base de datos mediate un like regresando una lista de objetos 
     * con los valores obtenidos y cerrando la conexion
     * 
     * Executes a query in the database using a like sentence and returns a List of Object with the
     * returned values from the Database and closing the connection
     * 
     * @param colums Array of strings that contains all the colums if null is
     * passed this select all the colums in the table
     * @param tableName Name of the table to select
     * @param condition Map<String,String> this is the selection to be aplied
     * in the database and should be in the forma key as column name and value
     * as 'X'value that should contain the database if null no selection is aply
     * @param groupBy this is the group by expression if null no group by is aply
     * @param having having setence to be aply
     * @param orderBy order by setence to be aply
     * @param limit limit if records setence to be aply
     * @return List of Object with the result
     * @exception SQLException if some database exception occurs
     * @exception Exception if something other than database exception occurs
     */
    public synchronized java.util.List<Object> likeQuery(String[] colums, String tableName,
            Map<String, String> conditions /* selection should be column as key and 'X'value as value of map */,
            String groupBy, String having, String orderBy, String limit) throws SQLException, Exception {
        
        return rawQuery(createLikeQueryString(colums, tableName, conditions, groupBy, having, orderBy, limit));
    }

    /**
     * Creates a string to query the database postgresql
     * Crea una cadena para la realizacion de consultas a una base de datos postgresql
     * 
     * @param colums Array of strings that contains all the colums if null is
     * passed this select all the colums in the table
     * @param tableName Name of the table to select
     * @param condition Map<String,String> this is the selection to be aplied
     * in the database and should be in the forma key as column name and value
     * as 'X'value that should contain the database if null no selection is aply
     * @param groupBy this is the group by expression if null no group by is aply
     * @param having having setence to be aply
     * @param orderBy order by setence to be aply
     * @param limit limit if records setence to be aply
     * @return String with the command to execute
     */
    private String createQueryString(String[] colums, String tableName, Map<String, String> conditions,
            String groupBy, String having, String orderBy, String limit) {
        StringBuilder sb = new StringBuilder("SELECT ");
        if (colums == null) { // if the value of colums is null the value is set to '*' means all the colums in table
            colums = new String[]{"*"};
        }

        for (String column : colums) {
            sb.append(column);
            if (!isLastItem(column, colums)) {
                sb.append(", ");
            }
        }
        sb.append(" FROM ").append(tableName).append(" ");
        
        if (conditions != null) {
            sb.append(" WHERE ");
            int index = 1; // this is used because map is not order but we want to know the last item
            for (Map.Entry<String, String> condition : conditions.entrySet()) {
                sb.append(condition.getKey()).append(" = ").append(condition.getValue());
                if (index != conditions.size()) {
                    sb.append(" AND ");
                }
                index++;
            }
        }
        if (groupBy != null) {
            sb.append(" GROUP BY ").append(groupBy);
        }
        if (having != null) {
            sb.append(" HAVING ").append(having);
        }
        if (orderBy != null) {
            sb.append(" ORDER BY ").append(orderBy);
        }
        if (limit != null) {
            sb.append(" LIMIT ").append(limit);
        }
        sb.append(";"); // Semicolon at the end =)
        return sb.toString();
    }
    
    /**
     * Creates a string to query the database postgresql
     * Crea una cadena para la realizacion de consultas a una base de datos postgresql
     * 
     * @param colums Array of strings that contains all the colums if null is
     * passed this select all the colums in the table
     * @param tableName Name of the table to select
     * @param condition Map<String,String> this is the selection to be aplied
     * in the database and should be in the forma key as column name and value
     * as 'X'value that should contain the database if null no selection is aply
     * @param groupBy this is the group by expression if null no group by is aply
     * @param having having setence to be aply
     * @param orderBy order by setence to be aply
     * @param limit limit if records setence to be aply
     * @return String with the command to execute
     */
    private String createLikeQueryString(String[] colums, String tableName, Map<String, String> conditions,
            String groupBy, String having, String orderBy, String limit) {
        StringBuilder sb = new StringBuilder("SELECT ");
        if (colums == null) { // if the value of colums is null the value is set to '*' means all the colums in table
            colums = new String[]{"*"};
        }

        for (String column : colums) {
            sb.append(column);
            if (!isLastItem(column, colums)) {
                sb.append(", ");
            }
        }
        sb.append(" FROM ").append(tableName).append(" ");
        
        if (conditions != null) {
            sb.append(" WHERE ");
            int index = 1; // this is used because map is not order but we want to know the last item
            for (Map.Entry<String, String> condition : conditions.entrySet()) {
                sb.append(condition.getKey()).append(" LIKE ('%").append(condition.getValue()).append("%')");
                if (index != conditions.size()) {
                    sb.append(" OR ");
                }
                index++;
            }
        }
        if (groupBy != null) {
            sb.append(" GROUP BY ").append(groupBy);
        }
        if (having != null) {
            sb.append(" HAVING ").append(having);
        }
        if (orderBy != null) {
            sb.append(" ORDER BY ").append(orderBy);
        }
        if (limit != null) {
            sb.append(" LIMIT ").append(limit);
        }
        sb.append(";"); // Semicolon at the end =)
        return sb.toString();
    }
    

    /**
     * Realiza una petición de modificación de datos, retornando
     * un int con el número de registros afectados.
     */
    public synchronized int ejecutarComando(String psStatement)
            throws Exception {

        int ret = 0;
        Vector vTransaction = new Vector();

        vTransaction.addElement(psStatement);
        ret = ejecutarComando(vTransaction);

        return ret;
    }

    /**
     * Realiza una serie de peticiones de modificación de datos, retornando
     * un int con el número de registros afectados.
     * Estas peticiones son ejecutadas todas en una transacción.
     */
    public synchronized int ejecutarComando(Vector pvStatement)
            throws Exception {

        int ret = 0, i = 0;
        Statement stmt = null;
        String temp = "";

        try {
            oConexion.setAutoCommit(false);
            stmt = oConexion.createStatement();
            for (i = 0; i < pvStatement.size(); i++) {
                temp = (String) pvStatement.elementAt(i);
                ret += stmt.executeUpdate(temp);
            }
            oConexion.commit();
        } catch (SQLException e) {
            oConexion.rollback();
            throw e;
        } finally {
            stmt.close();
            stmt = null;
        }

        return ret;
    }

    /**
     * Iterates over a resultSet and convert it a ArrayList
     * @param resultSet result that cames from the database this
     * @param resultSet this is the metadata of the result set
     * @return 
     * @exception Exception throws an exception if something wrong happend
     */
    public synchronized ArrayList<Object> toArrayList(ResultSet resultSet, ResultSetMetaData resultSetMetaData) throws Exception {
        ArrayList<Object> result = new ArrayList<Object>();
        ArrayList temp = null;
        
        int numColumns = resultSetMetaData.getColumnCount();
        while (resultSet.next()) {            
            temp = new ArrayList(resultSetMetaData.getColumnCount());
            for (int i = 1; i <= numColumns; i++) {
                switch (resultSetMetaData.getColumnType(i)){
                    case Types.CHAR:
                    case Types.VARCHAR:
                        String varchar = "" + doubleQuote(resultSet.getString(i));
                        temp.add(varchar);
                        break;
                    case Types.INTEGER:
                        temp.add(new Integer(resultSet.getInt(i)));
                        break;
                    case Types.SMALLINT:
                        temp.add(new Double(resultSet.getInt(i)));
                        break;
                    case Types.BIGINT:
                    case Types.NUMERIC:
                    case Types.DECIMAL:
                    case Types.DOUBLE:
                        temp.add(new Double(resultSet.getDouble(i)));
                        break;
                    case Types.DATE:
                    case Types.TIME:
                    case Types.TIMESTAMP:
                        temp.add((resultSet.getTimestamp(i) == null ? null : new Date(resultSet.getTimestamp(i).getTime())));
                        break;
                    case Types.BINARY:
                        temp.add(resultSet.getBytes(i));
                        break;
                    default:
                        String str = "" + resultSet.getString(i);
                        temp.add(str);
                }
            }
            result.add(temp);
        }
        return result;
    }
    
    /**
     * Recorre un result set y entrega el vector resultante.
     */
    private synchronized Vector convierteAVector(ResultSet rset,
            ResultSetMetaData rsmd,
            int nNumCols)
            throws Exception {
        Vector vrset = new Vector();
        Vector vrsettmp = null;
        int i = 0;

        while (rset.next()) {
            vrsettmp = new Vector();
            for (i = 1; i <= nNumCols; i++) {

                switch (rsmd.getColumnType(i)) {
                    case Types.CHAR:
                    case Types.VARCHAR:
                        String varchar = "" + doubleQuote(rset.getString(i));
                        vrsettmp.addElement(varchar);
                        break;
                    case Types.INTEGER:
                        vrsettmp.addElement(new Integer(rset.getInt(i)));
                        break;
                    case Types.SMALLINT:
                        vrsettmp.addElement(new Double(rset.getInt(i)));
                        break;
                    case Types.BIGINT:
                    case Types.NUMERIC:
                    case Types.DECIMAL:
                    case Types.DOUBLE:
                        vrsettmp.addElement(new Double(rset.getDouble(i)));
                        break;
                    case Types.DATE:
                    case Types.TIME:
                    case Types.TIMESTAMP:
                        vrsettmp.addElement((rset.getTimestamp(i) == null ? null : new Date(rset.getTimestamp(i).getTime())));
                        break;
                    case Types.BINARY:
                        vrsettmp.addElement(rset.getBytes(i));
                        break;
                    default:
                        String str = "" + rset.getString(i);
                        vrsettmp.addElement(str);
                } //switch  
            }  //for
            vrset.addElement(vrsettmp);
        } //while
        return vrset;
    }

    /**
     * Imprime en forma adecuada este objeto.
     * @return String los datos del objeto.
     */
    @Override
    public String toString() {
        StringBuilder s = new StringBuilder();
        s.append("Class = DataAccess \n");
        s.append("    static sUrl  = ").append(sUrl).append("\n");
        s.append("    static sUsr = ").append(sUsr).append("\n");
        s.append("    static sPwd  = ").append(sPwd).append("\n");
        s.append("    oConexion = ").append(oConexion).append("\n");
        return s.toString();
    }

    /**
     * Si la cadena contiene comillas en la base de datos, convierte a código.
     * @return String cadena sin las comillas internas.
     */
    private String doubleQuote(String psCadena) {
        if (psCadena == null) {
            psCadena = "";
        }
        String CadenaEntrada = "";
        if (psCadena.equals("")) {
            return psCadena;
        } else if (psCadena.equals("\"")) {
            return "&quot;";
        } else {
            int indice = -2;
            CadenaEntrada = psCadena;
            while ((indice = CadenaEntrada.indexOf("\"", indice + 2)) != -1) {
                CadenaEntrada = CadenaEntrada.substring(0, CadenaEntrada.indexOf("\"", indice)) + "&quot;" + CadenaEntrada.substring(CadenaEntrada.indexOf("\"", indice) + 1);
            }
        }
        return CadenaEntrada;
    }

    /**
     * Check if the given element is the last of an array
     * 
     * @param element element to be compared
     * @param collection collection of items
     * @return the given value is equals to the collection in
     * index length -1 also, if collection is null returns false
     */
    private boolean isLastItem(Object element, Object[] collection) {
        if (collection == null) {
            return false;
        }
        return element.equals(collection[collection.length - 1]);
    }
    
    /**
     * Execute a single SQL statement that is not a query. For example, CREATE TABLE, DELETE, INSERT, etc.
     * 
     * @return number of rows affected
     * @throws SQLException 
     */
    public synchronized int executeSQL(String sql) throws SQLException{
        int rowsAffected = 0;
        Statement stmt = null;
        try{
            stmt = oConexion.createStatement();
            rowsAffected = stmt.executeUpdate(sql);
        }
        finally{
            if(stmt != null){
                stmt.close();
                stmt = null;
            }
        }
        return rowsAffected;
    }
    
    /**
     * Executes a query into the database using the string query as command
     * @param query command to be executed in the database
     * @return List of Object with the result
     * @throws SQLException if some Database exception occurs
     * @throws Exception if some Exception other than database occurs
     */
    public synchronized List<Object> rawQuery(String query) throws SQLException, Exception{
        Statement stmt = null;
        ResultSet rset = null;
        ResultSetMetaData rsmd = null;
        ArrayList<Object> list = new ArrayList<Object>();
        
        try {
            stmt = oConexion.createStatement();
            rset = stmt.executeQuery(query);
            rsmd = rset.getMetaData();
            list = toArrayList(rset, rsmd);
        } finally {
            if (rset != null) {
                rset.close();
                stmt.close();
            }
            rset = null;
            stmt = null;
        }
        return list;
    }
    
    /**
     * Insert a given set of items sort in a map where key is column name and
     * value is the value to insert
     * @param tableName
     * @param values
     * @return
     * @throws SQLException 
     */
    public synchronized int insert(String tableName, Map<String,Object> values) throws SQLException{
        int rowsAffected = 0;
        PreparedStatement pstmt = null;
        
        try{
            String statement = createInsertString(tableName, values);
            pstmt = oConexion.prepareStatement(statement);
            List<Object> elements = new ArrayList<Object>(values.values());
            int i = 1;
            for (Iterator<Object> element = elements.iterator(); element.hasNext(); i++) {
                setObjectIntoPreparedStatement(pstmt, element.next(), i);
            }
            rowsAffected = pstmt.executeUpdate();
        } catch (SQLException e) {
            oConexion.rollback();
            throw e;
        } finally {
            pstmt.close();
            pstmt = null;
        }
        return rowsAffected;
    }

    /**
     * Update values into datbase using a given set of items sort in a map where key is column name and
     * value is the value to be set and if exist conditions fallow the same rules as set items
     * @param tableName Name of table to be updated
     * @param values values that are going to be set
     * @return number of rows affected
     * @throws SQLException if some database exception occurs
     */
    public synchronized int update(String tableName, Map<String,Object> values, Map<String,Object> conditions) throws SQLException{
        int rowsAffected = 0;
        PreparedStatement pstmt = null;
        
        try{
            String statement = createUpdateString(tableName, values, conditions);
            pstmt = oConexion.prepareStatement(statement);
            List<Object> elements = new ArrayList<Object>(values.values());
            int i = 1;
            for (Iterator<Object> element = elements.iterator(); element.hasNext(); i++) {
                setObjectIntoPreparedStatement(pstmt, element.next(), i);
            }
            
            List<Object> contionsElements = new ArrayList<Object>(conditions.values());
            for (Iterator<Object> it = contionsElements.iterator(); it.hasNext(); i++ ) {
                setObjectIntoPreparedStatement(pstmt, it.next(), i);
            }
            
            rowsAffected = pstmt.executeUpdate();
        } catch (SQLException e) {
            oConexion.rollback();
            throw e;
        } finally {
            pstmt.close();
            pstmt = null;
        }
        return rowsAffected;
    }
    
    public synchronized int delete(String tableName, Map<String, Object> conditions) throws SQLException{
        int rowsAffected = 0;
        PreparedStatement pstmt = null;
        
        try{
            String statement = createDeleteString(tableName, conditions);
            pstmt = oConexion.prepareStatement(statement);            
            List<Object> contionsElements = new ArrayList<Object>(conditions.values());
            int i = 1;
            for (Iterator<Object> it = contionsElements.iterator(); it.hasNext(); i++ ) {
                setObjectIntoPreparedStatement(pstmt, it.next(), i);
            }
            
            rowsAffected = pstmt.executeUpdate();
        } catch (SQLException e) {
            oConexion.rollback();
            throw e;
        } finally {
            pstmt.close();
            pstmt = null;
        }
        return rowsAffected;
    }
    
    /**
     * Creates an insert string to be used as a prepared statement (contains '?')
     * @param tableName Name of the table to be inserted
     * @param values map containing the values to be inserted this should be in
     * the format key as column name and value as value to be inserted
     * @return String to be used as a prepared statement
     */
    private String createInsertString(String tableName, Map<String, Object> values) {
        if (values == null) {
            return new String();
        }
        
        StringBuilder sb = new StringBuilder("INSERT INTO ").append(tableName);
        sb.append(" (");
        
        List<String> keys = new ArrayList<String>(values.keySet());
        for (Iterator<String> it = keys.iterator(); it.hasNext();) {
            sb.append(it.next());
            if (it.hasNext()) {
                sb.append(", ");
            }
        }
        
        sb.append(") VALUES (");
        
        List<Object> elemts = new ArrayList<Object>(values.values());
        for (Iterator<Object> element = elemts.iterator(); element.hasNext();) {
            sb.append("?");            
            element.next();
            if (element.hasNext()) {
                sb.append(", ");
            }
        }
        sb.append(");");
        
        return sb.toString();
    }
    
    /**
     * Creates an udpdate String statement to be used as Prepared Statement (contains '?')
     * @param tableName name of the table to be updated
     * @param values values that are going to be set
     * @param conditions the conditions if exist some to update if null is 
     * passed this update the entired table
     * @return String containing the statement
     */
    private String createUpdateString(String tableName, Map<String, Object> values, Map<String, Object> conditions) {
        if(values == null){
            return new String();
        }
        
        StringBuilder sb = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
        
        List<String> keys = new ArrayList<String>(values.keySet());       
        for (Iterator<String> it = keys.iterator(); it.hasNext();) {
            sb.append(it.next());
            sb.append(" = ?");
            if (it.hasNext()) {
                sb.append(", ");
            }
        }
        
        if (conditions != null && conditions.size() > 0) {
            sb.append(" WHERE ");
            
            List<String> conditionsKeys = new ArrayList<String>(conditions.keySet());
            for (Iterator<String> it = conditionsKeys.iterator(); it.hasNext();) {
                sb.append(it.next());
                sb.append(" = ?");
                if (it.hasNext()) {
                    sb.append(" AND ");
                }
            }
        }
        
        sb.append(";");
        
        return sb.toString();
    }
    
    /**
     * Creates a delete string statement that is used as a prepared statement
     * @param tableName table name to be apply the delete setence
     * @param conditions this are the where clause witch are apply to the
     * delte statement 
     * @return String statement to be used as a prepared statement contains '?'
     */
    private String createDeleteString(String tableName, Map<String, Object> conditions) {
        StringBuilder sb = new StringBuilder("DELETE FROM ").append(tableName); // This clear all the table so watch out
        
        if (conditions != null && conditions.size() > 0) {
            sb.append(" WHERE ");
            
            List<String> conditionsKeys = new ArrayList<String>(conditions.keySet());
            for (Iterator<String> it = conditionsKeys.iterator(); it.hasNext();) {
                sb.append(it.next());
                sb.append(" = ?");
                if (it.hasNext()) {
                    sb.append(" AND ");
                }
            }
        }
        
        return sb.toString();
    }

    /**
     * Insert te given obj into the prepared statement in the given index chechkin the object type and using the
     * correct method if is posible
     * @param pstmt Statement in with the objects are set
     * @param obj object to be set in the given index
     * @param index index in witch the obj is set
     * @throws SQLException if some exception happends
     */
    private synchronized void setObjectIntoPreparedStatement(PreparedStatement pstmt, Object obj, int index) throws SQLException {
        if (obj != null) {
            if (obj instanceof Number) {
                Number tempNumber = (Number) obj;
                if (tempNumber instanceof Byte) {
                    pstmt.setByte(index, tempNumber.byteValue());
                }
                else if (tempNumber instanceof Short) {
                    pstmt.setShort(index, tempNumber.shortValue());
                }
                else if (tempNumber instanceof Integer) {
                    pstmt.setInt(index, tempNumber.intValue());
                }
                else if (tempNumber instanceof Long) {
                    pstmt.setLong(index, tempNumber.longValue());
                }
                else if (tempNumber instanceof Float) {
                    pstmt.setFloat(index, tempNumber.floatValue());
                }
                else if (tempNumber instanceof Double){
                    pstmt.setDouble(index, tempNumber.doubleValue());
                }
            }
            else if(obj instanceof CharSequence){
                pstmt.setString(index, obj.toString());
            }
            else if(obj instanceof java.util.Date){
                java.util.Date date = (java.util.Date) obj;
                Date sqlDate = new Date(date.getTime());
                pstmt.setDate(index, sqlDate);
            }
            else if(obj instanceof byte[]){
                byte[] bytes = (byte[])obj;
                pstmt.setBytes(index, bytes);
            }
            else {
                pstmt.setObject(index, obj);
            }
        }
        else
        {
            pstmt.setNull(index, Types.NULL);
        }
        
    }

    
}